#!/bin/bash
source /etc/profile

dt="`date -d "$d  0 days ago "  "+%Y-%m-%d"`"
mysql -uroot -proot -hhadoop2<<EOF
use sqlserver;
select substr(X.transaction_date,1,16),X.station_id,X.product_code,count(X.product_code),sum(X.item_quantity),sum(X.item_value)
from
(select Z.transaction_date,Y.station_id,Y.transaction_id,Y.product_code,Y.item_quantity,Y.item_value
from 
( SELECT transaction_date,transaction_id,station_id from transactions where substr(transaction_date,1,10)='$dt' )as Z 
join transaction_items_oil as Y
on Z.transaction_id=Y.transaction_id and Z.station_id=Y.station_id)AS X
group by substr(X.transaction_date,1,16),X.station_id,X.product_code ;
EOF
#awk ' NR !=1 {print "('\''"$1,$2"'\'',","'\''"$3"'\'',","'\''"$4"'\'',",$5,","$6,","$7")"}' mysql.log >tmp.log
#awk ' NR !=1 {print "INSERT into test.res_oil VALUES('\''"$1,$2"'\'',","'\''"$3"'\'',","'\''"$4"'\'',",$5,","$6,","$7") on duplicate key update oilNumber="$5",oilLitre="$6",oilMoney="$7";"}' mysql.log >insert.sql
